
package fr.greta.centre.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.naming.NamingException;


import fr.greta.centre.metier.ActionsFormation;
import fr.greta.centre.metier.Formateur;
import fr.greta.centre.metier.Module;

public class ActionsFormationDAOImpl implements ActionsFormationDAO{

	@Override
	public int save(ActionsFormation actionsFormation) throws SQLException {
		assert actionsFormation != null;
		int i = 0;
		try {
			Module mod = new Module();
			Statement st = Bd.getConnect().createStatement();
			System.out.println(st);
			String s = "INSERT INTO `centre_formation`.`action_formation` (`id_formateur`,`libelle`,`id_module`) VALUES ('"
					+ actionsFormation.getFormateur().getId()
					+ "', '"
					+ actionsFormation.getLibelle()
					+ "','"
					+ mod.getIdModule()
					+  "')";
			System.out.println(s);
			st.executeUpdate(s);

			String ss = "SELECT MAX(id_action_formation) AS LastID FROM action_formation";
			ResultSet r = st.executeQuery(ss);
			r.next();
			i = r.getInt(i);
		}

		catch (SQLException e) {
			System.out.println(e.getMessage());
			System.out.println("insert ok" + " " + i);
			return i;
		}
		return 0;
	}

	@Override
	public boolean delete(int id) throws SQLException {
				
		String sql = "DELETE FROM action_formation WHERE id_action_formation = "+ id;
		try {
			Connection con = Bd.getConnect();
			Statement st = con.createStatement();
			st.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return false;
	}

	@Override
	public ActionsFormation findByKey(int id) throws SQLException {

		ActionsFormation uneAction = null;
		Connection connec;
		connec = Bd.getConnect();
		Statement statement = connec.createStatement();
		String sql = "SELECT * FROM action_formation WHERE id_action_formation = "
				+ id + "";
		ResultSet resultat = statement.executeQuery(sql);
		resultat.next();
		uneAction = new ActionsFormation();
		Module unModule = new Module();
		unModule.setIdModule(resultat.getInt("id_module"));
		Formateur unFormateur = new Formateur();
		unFormateur.setId(resultat.getInt("id_formateur"));
		uneAction.setIdActionFormation(resultat.getInt("id_action_formation"));
		uneAction.setLibelle(resultat.getString("libelle"));
		uneAction.setFormateur(unFormateur);
		uneAction.setModule(unModule);
		

		return uneAction;
	}

	@Override
	public List<ActionsFormation> findAll() throws SQLException {
		
		ResultSet resultat;
		ArrayList<ActionsFormation> lesActions = new ArrayList<ActionsFormation>();
		
		try {
			Connection connect = Bd.getConnect();
			String sql = "SELECT * FROM action_formation ";
			resultat = connect.createStatement().executeQuery(sql);
			while( resultat.next()){
				Formateur unFormateur = new Formateur();
				unFormateur.setId(resultat.getInt("id_formateur"));
				Module unModule=new Module();
				unModule.setIdModule(resultat.getInt("id_module"));		
				ActionsFormation uneAction = new ActionsFormation();
				uneAction.setLibelle(resultat.getString("libelle"));
				uneAction.setIdActionFormation(resultat.getInt("id_action_formation"));
				uneAction.setFormateur(unFormateur);
				uneAction.setModule(unModule);
				lesActions.add(uneAction);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return lesActions;	
	}

	@Override
	public ArrayList<ActionsFormation> findByIdModule(int idModule) {
		String sql = "SELECT * FROM action_formation" +
				"WHERE id_module = ?";
		PreparedStatement st;
		try {
			st = Bd.getConnect().prepareStatement(sql);
			st.setInt(1, idModule);
			ResultSet rs = st.executeQuery();
			ArrayList<ActionsFormation> lesActionsFormation = new ArrayList<ActionsFormation>();
			
			while(rs.next()){
				Formateur leFormateur = new FormateurDAOImpl().findByKey(rs.getInt("id_formateur"));
				Module leModule = new ModuleDAOImpl().findByKey(rs.getInt("id_module"));
				ActionsFormation uneActionFormation = new ActionsFormation(leFormateur,
						rs.getInt("id_action_formation"), rs.getString("libele"), leModule);
				lesActionsFormation.add(uneActionFormation);
			}
			return lesActionsFormation;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return null;
	}

}

